from decimal import Decimal

from app.service import app_payment
from libs import time_helper, common
from libs.db import DB

SELL_STATE = '1,2,3,7'
RETURN_STATE = '4,5,9,10,11'


# 订单统计
def stats_order(cashier_id, from_ts, to_ts, cursor,shop_id):
    from_time = time_helper.ts_to_time_str(from_ts, '%Y-%m-%d %H:%M:%S')
    to_time = time_helper.ts_to_time_str(to_ts, '%Y-%m-%d %H:%M:%S')
    storage_0_result = stats_storage(cursor,shop_id,from_time,to_time)
    cash_total_num = stats_cs_cash_total_num(cursor, cashier_id, from_ts, to_ts)
    cash_total_amount = stats_cs_cash_total_amount(cursor, cashier_id, from_ts, to_ts)
    # 应有现金
    cash_info = {
        'label': '现金流水',
        'num': cash_total_num,
        'amount': cash_total_amount,
        'detail': []
    }

    sale_1_num = stats_sale_1_num(cursor, cashier_id, from_ts, to_ts)
    sale_1_amount = stats_sale_1_amount(cursor, cashier_id, from_ts, to_ts)
    sale_info = {
        'label': '总销售流水',
        'num': sale_1_num,
        'amount': sale_1_amount,
        'detail': []
    }

    storage_info = {
        'label': '仓储卡充值',
        'num': storage_0_result.get('num'),
        'amount': storage_0_result.get('amount')
    }

    pay_1_num = stats_pay_1_num(cursor, cashier_id, from_ts, to_ts)
    pay_1_amount = stats_pay_1_amount(cursor, cashier_id, from_ts, to_ts)
    payment_info = {
        'label': '支付统计',
        'num': pay_1_num,
        'amount': pay_1_amount,
        'detail': []
    }

    sale_2_num = stats_sale_2_num(cursor, cashier_id, from_ts, to_ts)
    if sale_2_num > 0:
        sale_2_amount = stats_sale_2_amount(cursor, cashier_id, from_ts, to_ts)
        sale_info.get('detail').append({
            'label': '实收总金额',
            'num': sale_2_num,
            'amount': sale_2_amount
        })

    sale_4_num = stats_sale_4_num(cursor, cashier_id, from_ts, to_ts)
    if sale_4_num > 0:
        sale_4_amount = stats_sale_4_amount(cursor, cashier_id, from_ts, to_ts)
        sale_info.get('detail').append({
            'label': '反结账',
            'num': sale_4_num,
            'amount': sale_4_amount
        })

    sale_3_num = stats_sale_3_num(cursor, cashier_id, from_ts, to_ts)
    if sale_3_num > 0:
        sale_3_amount = stats_sale_3_amount(cursor, cashier_id, from_ts, to_ts)
        sale_info.get('detail').append({
            'label': '退换货总金额',
            'num': sale_3_num,
            'amount': sale_3_amount
        })

    pay_num_map = stats_pay_num_by_pay_method_typ(cursor, cashier_id, from_ts, to_ts)
    pay_amount_map = stats_amount_by_pay_method_typ(cursor, cashier_id, from_ts, to_ts)

    pay_mt1_num = pay_num_map.get(1, 0)
    if pay_mt1_num > 0:
        pay_mt1_amount = pay_amount_map.get(1, 0)
        payment_info.get('detail').append({
            'label': '现金',
            'num': pay_mt1_num,
            'amount': pay_mt1_amount
        })

    pay_mt2_num = pay_num_map.get(2, 0)
    if pay_mt2_num > 0:
        pay_mt2_amount = pay_amount_map.get(2, 0)
        payment_info.get('detail').append({
            'label': '仓储卡',
            'num': pay_mt2_num,
            'amount': pay_mt2_amount
        })

    pay_mt3_num = pay_num_map.get(3, 0)
    if pay_mt3_num > 0:
        pay_mt3_amount = pay_amount_map.get(3, 0)
        payment_info.get('detail').append({
            'label': '余额',
            'num': pay_mt3_num,
            'amount': pay_mt3_amount
        })

    pay_mt10_num = pay_num_map.get(10, 0)
    if pay_mt10_num > 0:
        pay_mt10_amount = pay_amount_map.get(10, 0)
        payment_info.get('detail').append({
            'label': '线下扫码',
            'num': pay_mt10_num,
            'amount': pay_mt10_amount
        })

    pay_mt100_num = pay_num_map.get(100, 0)
    if pay_mt100_num > 0:
        pay_mt100_amount = pay_amount_map.get(100, 0)
        payment_info.get('detail').append({
            'label': '微信/支付宝',
            'num': pay_mt100_num,
            'amount': pay_mt100_amount
        })

    pay_mt200_num = pay_num_map.get(200, 0)
    if pay_mt200_num > 0:
        pay_mt200_amount = pay_amount_map.get(200, 0)
        payment_info.get('detail').append({
            'label': '快餐店会员卡',
            'num': pay_mt200_num,
            'amount': pay_mt200_amount
        })
    
    cash_1_num = stats_cs_cash_1_num(cursor, cashier_id, from_ts, to_ts)
    if cash_1_num > 0:
        cash_1_amount = stats_cs_cash_1_amount(cursor, cashier_id, from_ts, to_ts)
        cash_info.get('detail').append({
            'label': '现金反结账',
            'num': cash_1_num,
            'amount': cash_1_amount
        })
    if cash_total_num > 0:
        cash_3_amount = stats_cs_cash_3_amount(cursor, cashier_id, from_ts, to_ts)
        cash_info.get('detail').append({
            'label': '现金实收金额',
            'num': cash_total_num,
            'amount': cash_3_amount
        })
    cash_2_num = stats_cs_cash_2_num(cursor, cashier_id, from_ts, to_ts)
    if cash_2_num > 0:
        cash_2_amount = stats_cs_cash_2_amount(cursor, cashier_id, from_ts, to_ts)
        cash_info.get('detail').append({
            'label': '现金退款金额',
            'num': cash_2_num,
            'amount': cash_2_amount
        })

    # print(order_id_arr)
    # print(cash_info)
    # print(pay_type_map)
    # print(order_status_stats_map)

    return {
        'cash': cash_info,
        'sale': sale_info,
        'storage': storage_info,
        'payment': payment_info,
        'from_time': from_time,
        'to_time': to_time,
    }


# 仓储卡充值统计
def stats_storage(cursor,shop_id,from_time,to_time,pay_mode=0):
    db = DB('storage_orders', conn=cursor)
    where = 'state>=0 and line_down=1 and shop_id=%s'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        where += ' and pay_time>=%s and pay_time<%s'
        #binds = [from_time, to_time]
        binds.append(from_time)
        binds.append(to_time)
    if pay_mode > 0:
        where += ' and pay_mode=%s'
        binds.append(pay_mode)
    num = db.count(where, binds)
    storage_amount = Decimal('0')
    if num > 0:
        storage_amount = db.val('sum(pay_amount)', where, binds)
        if storage_amount is None:
            storage_amount = Decimal('0')
    result = {
        'amount': storage_amount,
        'num': num
    }
    return result


# 统计店铺销售金额
def stats_shop_sales_amount(cursor, shop_id, from_time, to_time, state='', filters=None, binds=None):
    db = DB('table_main_orders', conn=cursor)
    if state != '':
        state_filter = ' and state in ({})'.format(state)
    else:
        state_filter = ''
    where = 'send_shop_id=%s{}'.format(state_filter)
    bindings = [shop_id]
    if from_time != '' and to_time != '':
        where += ' and order_time>=%s and order_time<%s'
        bindings += [from_time, to_time]
    if type(filters) is list:
        filter_str = ' and ' + ' and '.join(filters)
        where += filter_str
    if type(binds) is list:
        bindings += binds
    sales_amount = db.val(
        'sum(abs(pay_usdt))',
        where,
        bindings)
    if sales_amount is None:
        sales_amount = 0
    return common.to_dec(sales_amount)


# 统计店铺收银系统销售金额
def stats_shop_cs_sales_amount(cursor, shop_id, from_time, to_time, status='', filters=None, binds=None):
    db = DB('cs_orders', conn=cursor)
    if status != '':
        status_filter = ' and `status` in ({})'.format(status)
    else:
        status_filter = ''
    where = 'shop_id=%s{}'.format(status_filter)
    bindings = [shop_id]
    if from_time != '' and to_time != '':
        from_ts = time_helper.time_str_to_ts(from_time)
        to_ts = time_helper.time_str_to_ts(to_time)
        where += ' and created>=%s and created<%s'
        bindings += [from_ts, to_ts]

    if type(filters) is list:
        filter_str = ' and ' + ' and '.join(filters)
        where += filter_str
    if type(binds) is list:
        bindings += binds
    sales_amount = db.val(
        'sum(abs(cart_total_price))',
        where,
        bindings)
    if sales_amount is None:
        sales_amount = 0
    return common.to_dec(sales_amount)


# 统计店铺收款明细(不包括已退款)
def stats_shop_payment(cursor, shop_id, from_time, to_time):
    stats_result = []
    sql = 'SELECT pay_mode,SUM(pay_usdt) money FROM `table_main_orders`' \
          ' WHERE send_shop_id=%s' \
          ' AND state IN ({})'.format(SELL_STATE)
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' and order_time>=%s and order_time<%s'
        binds += [from_time, to_time]
    sql += ' GROUP BY pay_mode'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    if rows is not None:
        get_pay_dict = app_payment.get_pay_dict(cursor)
        total = 0
        is_zuhe = 0
        money_type = {1:0,2:0,3:0,4:0,5:0,6:0,7:0} # 1 现金 2 仓储卡 3 余额 4 线下扫码 5 微信 6 支付宝 7 会员卡
        for row in rows:
            stats_item = {}
            pay_mode, money = row
            if pay_mode > 0 and money is not None:
                money_dec = common.to_dec(money)
                if pay_mode == 99:
                    is_zuhe = 1
                else:
                    stats_item.update({
                        'pay_typ': get_pay_dict.get(pay_mode, ''),
                        'pay_mode': pay_mode,
                        'money': money_dec
                    })
                    stats_result.append(stats_item)
                total += money_dec
        if is_zuhe == 1:
            sql1 = 'SELECT b.pay_method_typ,b.pay_typ_id,b.pay_amount from table_main_orders as a left join cs_order_pay_logs as b' \
            ' on a.cs_order_id = b.order_id where a.send_shop_id=%s and a.pay_mode = 99' \
            ' and state IN ({})'.format(SELL_STATE)
            binds1 = [shop_id]
            if from_time != '' and to_time != '':
                sql1 += ' and a.order_time>=%s and a.order_time<%s'
                binds1 += [from_time, to_time]
            cursor.execute(sql1,binds1)
            rows1 = cursor.fetchall()
            for row1 in rows1:
                pay_method_typ,pay_typ_id,pay_amount = row1
                pay_amount = common.to_dec(pay_amount)
                if pay_method_typ == 1:
                    money_type[1] += pay_amount
                elif pay_method_typ == 2:
                    money_type[2] += pay_amount
                elif pay_method_typ == 3:
                    money_type[3] += pay_amount
                elif pay_method_typ == 10:
                    money_type[4] += pay_amount
                elif pay_method_typ == 100:
                    if pay_typ_id == 1:
                        money_type[5] += pay_amount
                    else:
                        money_type[6] += pay_amount
                elif pay_method_typ == 200:
                    money_type[7] += pay_amount
            for item in money_type.items():
                stats_item1 = {}
                key = item[0]
                val = item[1]
                check_sign = 0
                if val > 0:
                    if key == 1:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 4 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(4, ''),
                                'pay_mode': 4,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 2:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 5 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(5, ''),
                                'pay_mode': 5,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 3:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 1 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(1, ''),
                                'pay_mode': 1,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 4:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 7 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(7, ''),
                                'pay_mode': 7,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 5:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 2 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(2, ''),
                                'pay_mode': 2,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 6:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 3 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(3, ''),
                                'pay_mode': 3,
                                'money': val
                            })
                            stats_result.append(stats_item1)
                    elif key == 7:
                        for res1  in stats_result:
                            if res1['pay_mode'] == 10 and res1['money'] > 0:
                              res1['money'] += val
                              check_sign = 1
                        if check_sign == 0:
                            stats_item1.update({
                                'pay_typ': get_pay_dict.get(10, ''),
                                'pay_mode': 10,
                                'money': val
                            })
                            stats_result.append(stats_item1)
        stats_result.append({
            'pay_typ': '总计',
            'pay_mode': 0,
            'money': total
        })
    return stats_result

# 统计店铺收款明细(包括已退款)
def stats_shop_payment_back(cursor, shop_id, from_time, to_time):
    stats_result = []
    sql = 'SELECT pay_mode,SUM(pay_usdt) money FROM `table_main_orders`' \
          ' WHERE send_shop_id=%s' \
          ' AND state IN ({})'.format(SELL_STATE)
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' and order_time>=%s and order_time<%s'
        binds += [from_time, to_time]
    sql += ' GROUP BY pay_mode'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    if rows is not None:
        get_pay_dict = app_payment.get_pay_dict(cursor)
        refund_map = stats_shop_refund_map(cursor, shop_id, from_time, to_time)
        total = 0
        for row in rows:
            stats_item = {}
            pay_mode, money = row
            if pay_mode > 0 and money is not None:
                money_dec = common.to_dec(money)
                refund_val = refund_map.get(pay_mode, 0)
                if refund_val > 0:
                    money_dec += common.to_dec(refund_val)
                stats_item.update({
                    'pay_typ': get_pay_dict.get(pay_mode, ''),
                    'pay_mode': pay_mode,
                    'money': money_dec
                })
                stats_result.append(stats_item)
                total += money_dec
        stats_result.append({
            'pay_typ': '总计',
            'pay_mode': 0,
            'money': total
        })
    return stats_result

# 统计店铺退货明细
def stats_shop_refund_map(cursor, shop_id, from_time, to_time):
    sql = 'SELECT pay_mode,SUM(pay_usdt) money FROM `table_main_orders`' \
          ' WHERE send_shop_id=%s' \
          ' AND state IN ({})'.format(RETURN_STATE)
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' and order_time>=%s and order_time<%s'
        binds += [from_time, to_time]
    sql += ' GROUP BY pay_mode'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    stats_map = {}
    if rows is not None:
        for row in rows:
            pay_mode, money = row
            if pay_mode > 0 and money is not None:
                stats_map.update({pay_mode: money})
    return stats_map


# 统计店铺会员数量
def stats_shop_member_num(cursor, shop_id, from_time='', to_time=''):
    db = DB('table_member_info', conn=cursor)
    where = 'shop_id=%s'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        where += ' and created_at>=%s and created_at<%s'
        binds += [from_time, to_time]
    return db.count(where, binds)


def stats_shop_rebate(cursor, shop_id, from_time='', to_time=''):
    sql_1 = 'SELECT SUM(reality) FROM one_rebate tuo LEFT JOIN table_orders tmi ON tuo.buy_id=tmi.id' \
            ' WHERE tmi.send_shop_id=%s'
    sql_2 = 'SELECT SUM(tuo.pay_amount * tmi.dedu_ratio) FROM storage_orders tuo LEFT JOIN storage_card tmi ON tuo.card_id=tmi.id' \
            ' WHERE tuo.shop_id=%s'
    sql_3 = 'SELECT SUM(reality) FROM storage_interest tuo LEFT JOIN table_member_info tmi ON tuo.username=tmi.username' \
            ' WHERE tmi.shop_id=%s'
    binds_1 = [shop_id]
    binds_2 = [shop_id]
    binds_3 = [shop_id]
    if from_time != '' and to_time != '':
        sql_1 += ' AND tuo.created_at>=%s AND tuo.created_at<%s'
        binds_1 += [from_time, to_time]
        sql_2 += ' AND tuo.pay_time>=%s AND tuo.pay_time<%s'
        binds_2 += [from_time, to_time]
        sql_3 += ' AND tuo.created_at>=%s AND tuo.created_at<%s'
        binds_3 += [from_time, to_time]
    val1 = 0
    val2 = 0
    val3 = 0
    cursor.execute(sql_1, binds_1)
    row1 = cursor.fetchone()
    if row1 is not None and row1[0] is not None:
        val1 = row1[0]
    cursor.execute(sql_2, binds_2)
    row2 = cursor.fetchone()
    if row2 is not None and row2[0] is not None:
        val2 = row2[0]
    cursor.execute(sql_3, binds_3)
    row3 = cursor.fetchone()
    if row3 is not None and row3[0] is not None:
        val3 = row3[0]
    return val1 + val2 + val3


def get_next_rebate_give_out_time(cursor):
    db = DB('table_setting_other', conn=cursor)
    row = db.one(['value'], 'id=3')
    next_rebate_give_out_ts = row.get('value', '')
    next_rebate_give_out_time = '0000-00-00'
    if next_rebate_give_out_ts != '':
        ts = int(next_rebate_give_out_ts)
        if ts > 0:
            ts += 1800
            next_rebate_give_out_time = time_helper.ts_to_time_str(ts, time_format='%Y-%m-%d %H:%M:%S')
    return next_rebate_give_out_time


# 待发放抵扣券的订单数量
def stats_rebate_to_be_give_out(cursor, shop_id, from_time='', to_time=''):
    sql = 'SELECT COUNT(*) FROM `table_orders` WHERE send_shop_id=%s AND state IN (1,3,7) AND is_return=0'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' AND order_time>=%s AND order_time<%s'
        binds += [from_time, to_time]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    num = 0
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 待发放抵扣券的订单数量
def sales_ranking(cursor, shop_id, from_time='', to_time=''):
    sql = 'SELECT * FROM' \
          ' (SELECT t1.goods_id,t1.goods_name,t1.usdt_price,t1.barcode,SUM(t1.sell_total)-SUM(IFNULL(s1.sell_number,0)) sell_total' \
          ',SUM(t1.total_amount)-SUM(IFNULL(s1.pay_usdt,0)) total_amount FROM (SELECT s.goods_id, g.goods_name, g.usdt_price, g.barcode,' \
          ' SUM(s.sell_number) sell_total,SUM(s.pay_usdt) total_amount FROM `table_orders` s INNER JOIN table_goods g ON g.id = s.goods_id' \
          ' WHERE s.pay_usdt > 0 AND s.send_shop_id=%s AND s.state IN ({})'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' AND s.order_time>=%s AND s.order_time<%s'
        binds += [from_time, to_time]
    sql += ' GROUP BY s.goods_id) t1 LEFT JOIN table_goods g1 ON g1.id = t1.goods_id' \
           ' LEFT JOIN `table_orders` s1 ON g1.id = s1.goods_id AND s1.state IN ({})' \
           ' AND s1.send_shop_id=%s'
    binds += [shop_id]
    if from_time != '' and to_time != '':
        sql += ' AND s1.order_time>=%s AND s1.order_time <%s'
        binds += [from_time, to_time]
    sql += ' GROUP BY t1.goods_id LIMIT 10) z WHERE z.sell_total>0 ORDER BY z.sell_total DESC'
    sql = sql.format(SELL_STATE, RETURN_STATE)
    cursor.execute(sql, binds)
    arr = []
    rows = cursor.fetchall()
    if rows is not None:
        for row in rows:
            (goods_id, goods_name, usdt_price, barcode, sell_total, total_amount) = row
            arr.append({
                'goods_id': goods_id,
                'sell_total': sell_total,
                'total_amount': total_amount,
                'goods_name': goods_name,
                'usdt_price': usdt_price,
                'barcode': barcode,
            })
    return arr


# 仓储卡当前余额
def stats_mem_storage_total(cursor):
    db = DB('table_member', conn=cursor)
    storage_total = db.val('sum(`storage`)')
    if storage_total is None:
        storage_total = Decimal('0')
    return storage_total


# 余额当前余额
def stats_mem_balance_total(cursor):
    db = DB('table_member', conn=cursor)
    storage_total = db.val('sum(`usdt_value`)')
    if storage_total is None:
        storage_total = Decimal('0')
    return storage_total

# 会员卡当前余额
def stats_mem_fastbalance_total(cursor):
    db = DB('table_member', conn=cursor)
    storage_total = db.val('sum(`fast_ballance`)')
    if storage_total is None:
        storage_total = Decimal('0')
    return storage_total

# 剩余抵扣券数量
def stats_mem_coupon_total(cursor):
    db = DB('table_member', conn=cursor)
    storage_total = db.val('sum(`coupon`)')
    if storage_total is None:
        storage_total = Decimal('0')
    return storage_total


# 抵扣券数量
def stats_mem_rebate_total(cursor, from_time='', to_time=''):
    db = DB('one_rebate', conn=cursor)
    filters = {'is_delete': 0}
    binds = []
    if from_time != '' and to_time != '':
        filters.update({
            'created_at>=%s': None,
            'created_at<%s': None,
        })
        binds += [from_time, to_time]
    rebate_total = db.val('sum(`reality`)', filters, binds)
    if rebate_total is None:
        rebate_total = float('0')
    return rebate_total


# 余额累计充值金额
def stats_balance_top_up_total(cursor):
    db = DB('top_up_orders', conn=cursor)
    storage_total = db.val('sum(`pay_amount`)')
    if storage_total is None:
        storage_total = Decimal('0')
    return storage_total

# 会员卡累计充值金额
def stats_fastballance_charge_total(cursor):
    db = DB('table_fastballance_flow', conn=cursor)
    filters = {'type': 1}
    rebate_total = db.val('sum(`amount`)', filters)
    if rebate_total is None:
        rebate_total = float('0')
    return rebate_total

# 有消费会员数量
def stats_have_order_member_num(cursor, shop_id, from_time='', to_time=''):
    sql = 'SELECT COUNT(DISTINCT member_id) FROM `table_main_orders` WHERE send_shop_id=%s AND member_id>0'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        sql += ' AND order_time>=%s AND order_time<%s'
        binds += [from_time, to_time]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    num = 0
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 统计抹零
def stats_price_to_int(cursor, shop_id, status='', from_time='', to_time=''):
    sql = 'SELECT sum(abs(total_price)),sum(abs(cart_total_price)) FROM `cs_orders` WHERE shop_id=%s'
    binds = [shop_id]
    if from_time != '' and to_time != '':
        from_ts = time_helper.time_str_to_ts(from_time)
        to_ts = time_helper.time_str_to_ts(to_time)
        sql += ' AND created>=%s AND created<%s'
        binds += [from_ts, to_ts]
    if status != '':
        sql += ' AND `status` in ({})'.format(status)
    cursor.execute(sql, binds)
    return cursor.fetchone()


# 统计收银现金总额
def stats_cs_cash_total_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) total_amount' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-100, -5, -1, 1) AND o.amount_sign>=0'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        [total_amount] = row
        amount_dec += common.to_dec(total_amount)
    return amount_dec


# 统计收银现金笔数
def stats_cs_cash_total_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 统计收银现金反结帐笔数
def stats_cs_cash_1_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-100)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 统计收银现金退款笔数
def stats_cs_cash_2_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-5, -1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 统计收银现金反结帐总金额
def stats_cs_cash_1_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) total_amount, o.status' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-100)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    status_map = {}
    for row in rows:
        total_amount, status = row
        status = int(status)
        if status in status_map:
            total_amount += status_map.get(status)
        status_map.update({status: total_amount})
    status_1 = status_map.get(-100, 0)
    return common.to_dec(status_1)


# 统计收银现金退货总金额
def stats_cs_cash_2_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) * amount_sign total_amount, o.status' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-5, -2)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    status_map = {}
    for row in rows:
        total_amount, status = row
        status = int(status)
        if status in status_map:
            total_amount += status_map.get(status)
        status_map.update({status: total_amount})
    status_1 = status_map.get(-5, 0)
    status_2 = status_map.get(-2, 0)
    return abs(common.to_dec(status_1) + common.to_dec(status_2))


# 统计收银现金实收总金额
def stats_cs_cash_3_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) * amount_sign total_amount, o.status' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND pl.`pay_method_typ`=1' \
          ' AND o.`status` IN (-5, -2, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        total_amount, _ = row
        amount_dec += common.to_dec(total_amount)
    return amount_dec


# 总销售流水 不考虑退款
def stats_sale_1_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) total_amount,o.status,o.amount_sign' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100, -5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        total_amount, status, amount_sign = row
        if amount_sign >= 0:
            amount_dec += common.to_dec(total_amount)
    return amount_dec


# 统计总流水
def stats_sale_1_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100, -5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


def stats_sale_2_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


def stats_sale_3_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -2)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


def stats_sale_4_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_orders` o' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


# 统计总流水，反结帐也计算在内
def stats_sale_2_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT' \
          ' SUM(ABS(pl.pay_amount)) total_amount, o.status, amount_sign' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -2, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        total_amount, _, amount_sign = row
        if amount_sign == 0:
            amount_dec += common.to_dec(total_amount)
        else:
            amount_dec += common.to_dec(total_amount) * common.to_dec(amount_sign)
    return amount_dec


# 退换货总额
def stats_sale_3_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT' \
          ' SUM(ABS(o.total_price)) total_amount' \
          ' FROM `cs_orders` o' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -2) AND o.amount_sign<0'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        [total_amount] = row
        amount_dec += common.to_dec(total_amount)
    return amount_dec


def stats_sale_4_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT' \
          ' SUM(ABS(o.total_price)) total_amount' \
          ' FROM `cs_orders` o' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        [total_amount] = row
        amount_dec += common.to_dec(total_amount)
    return amount_dec


# 统计支付总笔数
def stats_pay_1_num(cursor, cashier_id, from_ts, to_ts):
    num = 0
    sql = 'SELECT count(*) num' \
          ' FROM `cs_order_pay_logs` pl' \
          ' LEFT JOIN `cs_orders` o ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100, -5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    cursor.execute(sql, binds)
    row = cursor.fetchone()
    if row is not None and row[0] > 0:
        num = row[0]
    return num


def stats_pay_1_amount(cursor, cashier_id, from_ts, to_ts):
    sql = 'SELECT SUM(ABS(pl.pay_amount)) total_amount,o.amount_sign' \
          ' FROM `cs_orders` o' \
          ' LEFT JOIN `cs_order_pay_logs` pl ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -2, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY o.`status`,o.amount_sign'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    amount_dec = Decimal('0')
    for row in rows:
        [total_amount, amount_sign] = row
        amount_dec += common.to_dec(total_amount) * common.to_dec(amount_sign)
    return amount_dec


def stats_pay_num_by_pay_method_typ(cursor, cashier_id, from_ts, to_ts):
    pay_stats_map = {}
    sql = 'SELECT count(*) num,pl.pay_method_typ' \
          ' FROM `cs_order_pay_logs` pl' \
          ' LEFT JOIN `cs_orders` o ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-100, -5, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY pl.pay_method_typ'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    for row in rows:
        [num, pay_method_typ] = row
        pay_stats_map.update({pay_method_typ: num})
    return pay_stats_map


def stats_amount_by_pay_method_typ(cursor, cashier_id, from_ts, to_ts):
    pay_stats_map = {}
    sql = 'SELECT sum(abs(pl.pay_amount) * o.amount_sign) total_amount,pl.pay_method_typ' \
          ' FROM `cs_order_pay_logs` pl' \
          ' LEFT JOIN `cs_orders` o ON o.id = pl.order_id' \
          ' WHERE o.cashier_id=%s' \
          ' AND o.`status` IN (-5, -2, -1, 1)'
    binds = [cashier_id]
    if from_ts > 0 and to_ts > 0:
        sql += ' AND o.created>=%s and o.created<%s'
        binds += [from_ts, to_ts]
    sql += ' GROUP BY pl.pay_method_typ'
    cursor.execute(sql, binds)
    rows = cursor.fetchall()
    for row in rows:
        [total_amount, pay_method_typ] = row
        pay_stats_map.update({pay_method_typ: total_amount})
    return pay_stats_map
